package rgbos.forum.persistance;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Date;
import java.util.Vector;
import rgbos.forum.domain.Auditor;
import rgbos.forum.domain.Topic;

public class SQLTopic{
	SqlTopicPost util_;
	
	public SQLTopic(){
		util_ = new SqlTopicPost();
	}
	public int saveNewTopic(Topic topic){
		if (util_.checkExistTopic(topic.getId_())){
			Auditor.Log("The topic " + topic.getId_() + " was ALLREADY added to the forum.", this.getClass());
    		return -1;
		}  
		
		Statement st = null;
		int val = 0;
		int id = -1;
		try {
			st = SQLUtil.conn_.createStatement();
			//int id = topic.getId_();
			String title = topic.getTitle_();
			String authorName = topic.getAuthorName();
			Date creationDate = new Date(topic.getCreationDate_().getTime());
			int postCount = topic.getPostCount();
			int viewCount = topic.getViewCount();

			val = st.executeUpdate("INSERT INTO topics (title,authorName,creationDate,postCount,viewCount)"+
					" VALUES ('" + title + "','" + authorName + "','" + creationDate + "','"+ postCount + "','"+viewCount +"')", Statement.RETURN_GENERATED_KEYS);

			ResultSet rs = st.getGeneratedKeys();

		    if (rs.next()) {
		        id = rs.getInt(1);
		    } else {
		    }
			Vector<Integer> posts = topic.getPostVector();
			for(int postid:posts){
				val = st.executeUpdate("INSERT INTO topicsPosts (postId,topicId) " +
						" VALUES ('" + postid + "','" + id + "')");
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return id;
		}

	    if ( val > 0 ){
	    	Auditor.Log("The topic " + topic.getId_() + " was added to the forum.", this.getClass());
	    	return id;
	    }
	    else return id;
	}
	public boolean modifyTopic(Topic topic){
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int id = topic.getId_();
			String title = topic.getTitle_();
			String authorName = topic.getAuthorName();
			Date creationDate = new Date(topic.getCreationDate_().getTime());
			int postCount = topic.getPostCount();
			int viewCount = topic.getViewCount();

			int rs = stmt.executeUpdate
	        ("UPDATE topics SET title='"+title+"',authorName='"+authorName+"',creationDate='"
	        +creationDate+"',postCount='"+postCount+"',viewCount='"+viewCount+"'"+
       		" WHERE id='"+id + "'");
			if (rs > 0){
				Auditor.Log("The topic " + topic.getId_() +"was successfully modified", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
		}
        return false;
	}
	public Topic getTopicByPost(int pid){
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT topicId FROM topicsPosts WHERE postId="+pid);
	        rs.next();
	        Topic topic=this.getTopicById(rs.getInt("topicId"));
	        return topic;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
	}
	
	public boolean deleteTopic(int topicId){
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate("DELETE FROM topics WHERE id='"+topicId+"'");

			// Maybe not good the one here :
			// stmt.executeUpdate("DELETE FROM topicsPosts WHERE topicId='"+topicId+"'");
			// shaul will erase all the topic's posts
			if (rs > 0){
				Auditor.Log("The topic " + topicId + " was deleted from the forum.", this.getClass());
				return true;
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
		Auditor.Log("The topic " + topicId + " was not in the Forum.", this.getClass());
		return false;
	}

	public Topic getTopicById(int topicId){
		java.sql.Statement stmt;
		java.sql.Statement stmt2;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM topics WHERE id='"+topicId+"'");
	        while (rs.next()){
	        	Vector<Integer> postsId = new Vector<Integer>();
	        	stmt2 = SQLUtil.conn_.createStatement();
	        	java.sql.ResultSet rs2 = stmt2.executeQuery
		        ("SELECT postId FROM topicsPosts WHERE topicId = '"+rs.getInt("id")+"'");
	        	while(rs2.next()){
	        		postsId.add(rs2.getInt("postId"));
	        	}
	        	Topic topic = new Topic(rs.getInt("id"), rs.getString("title"),
	        			rs.getString("authorName"),rs.getDate("creationDate"),postsId,
	        			rs.getInt("postCount"),rs.getInt("viewCount"));
	        	return topic;
	        }
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return null;
		}
        return null;
	}

	public Vector<Topic> getTopics(){
		Vector<Topic> result = new Vector<Topic>();
		java.sql.Statement stmt;
		java.sql.Statement stmt2;
		try {
			stmt = SQLUtil.conn_.createStatement();
			java.sql.ResultSet rs = stmt.executeQuery
	        ("SELECT * FROM topics");
	        while (rs.next()){
	        	Vector<Integer> postsId = new Vector<Integer>();
	        	stmt2 = SQLUtil.conn_.createStatement();

	        	java.sql.ResultSet rs2 = stmt2.executeQuery
		        ("SELECT * FROM topicsPosts WHERE topicId='"+rs.getInt("id")+"'");
	        	while(rs2.next()){
	        		postsId.add(rs2.getInt("postId"));
	        	}
	        	Topic topic = new Topic(rs.getInt("id"), rs.getString("title"),
	        			rs.getString("authorName"),rs.getDate("creationDate"),postsId,
	        			rs.getInt("postCount"),rs.getInt("viewCount"));
	        	result.add(topic);
	        	//System.out.println(topic.getTopicAsString());
	        }
	        return result;
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return result;
		}
	}
	public void addViewCount(int topicId) {
		java.sql.Statement stmt;
		try {
			stmt = SQLUtil.conn_.createStatement();
			int rs = stmt.executeUpdate
	        ("UPDATE topics SET viewCount=viewCount+1 WHERE id="+topicId);
			if (rs > 0){
				Auditor.Log("The topic " + topicId +"was successfully inc view count", this.getClass());
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

}
